using System;
using System.Collections.Generic;
using System.Text;
using ECommerce.DAL;
using System.Data.SqlClient;
using System.Data;

namespace Entity
{
    [System.ComponentModel.DataObject]
    public class TblArticleExtended : TblArticle
    {
        public static CustomCollection<TblArticle> GetActiveArticleByCount(int howMany)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@howMany", SqlDbType.Int, 0, ParameterDirection.Input, howMany);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_ARTICLE_GET_ACTIVE_ARTICLE_BY_COUNT);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblArticleCollectionFromReader);
            CustomCollection<TblArticle> objCollection = ((CustomCollection<TblArticle>)ExecuteReaderCmd(sqlCmd, reader));
            return objCollection;
        }

        public static int GetActiveArticleCount()
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_ARTICLE_GET_ACTIVE_ARTICLE_COUNT);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static CustomCollection<TblArticle> GetNonDeletedArticlePaged(string orderBy, int startRow, int pageSize)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_ARTICLE_GET_NONDELETED_ARTICLE_PAGED);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblArticleCollectionFromReader);
            CustomCollection<TblArticle> objCollection = ((CustomCollection<TblArticle>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }
        
        public static int GetNonDeletedArticleCount()
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_ARTICLE_GET_NONDELETED_ARTICLE_COUNT);
            
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static CustomCollection<TblArticle> GetAllArticlesByArticleNamePaged(string articleName, string orderBy, int startRow, int pageSize)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@articleName", SqlDbType.NVarChar, 500, ParameterDirection.Input, articleName);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_ARTICLE_GET_ALL_ARTICLE_BY_NAME_PAGED);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblArticleCollectionFromReader);
            CustomCollection<TblArticle> objCollection = ((CustomCollection<TblArticle>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetAllArticlesByArticleNameCount(string articleName)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@articleName", SqlDbType.NVarChar, 500, ParameterDirection.Input, articleName);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_ARTICLE_GET_ALL_ARTICLE_BY_NAME_COUNT);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static CustomCollection<TblArticle> GetNonDeletedArticlesByArticleNamePaged(string articleName, string orderBy, int startRow, int pageSize)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@articleName", SqlDbType.NVarChar, 500, ParameterDirection.Input, articleName);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_ARTICLE_GET_NONDELETED_ARTICLE_BY_NAME_PAGED);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblArticleCollectionFromReader);
            CustomCollection<TblArticle> objCollection = ((CustomCollection<TblArticle>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetNonDeletedArticlesByArticleNameCount(string articleName)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@articleName", SqlDbType.NVarChar, 500, ParameterDirection.Input, articleName);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_ARTICLE_GET_NONDELETED_ARTICLE_BY_NAME_COUNT);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        private const string SP_TBL_ARTICLE_GET_ACTIVE_ARTICLE_BY_COUNT = "sp_TblArticle_Get_Active_Article_By_Count";
        private const string SP_TBL_ARTICLE_GET_ACTIVE_ARTICLE_COUNT = "sp_TblArticle_Get_Active_Article_Count";
        private const string SP_TBL_ARTICLE_GET_NONDELETED_ARTICLE_PAGED = "sp_TblArticle_Get_NonDeleted_Article_Paged";
        private const string SP_TBL_ARTICLE_GET_NONDELETED_ARTICLE_COUNT = "sp_TblArticle_Get_NonDeleted_Article_Count";
        private const string SP_TBL_ARTICLE_GET_ALL_ARTICLE_BY_NAME_PAGED = "sp_TblArticle_Get_All_Article_By_Name_Paged";
        private const string SP_TBL_ARTICLE_GET_ALL_ARTICLE_BY_NAME_COUNT = "sp_TblArticle_Get_All_Article_By_Name_Count";
        private const string SP_TBL_ARTICLE_GET_NONDELETED_ARTICLE_BY_NAME_PAGED = "sp_TblArticle_Get_NonDeleted_Article_By_Name_Paged";
        private const string SP_TBL_ARTICLE_GET_NONDELETED_ARTICLE_BY_NAME_COUNT = "sp_TblArticle_Get_NonDeleted_Article_By_Name_Count";
        
    }
}
